package sdu.attendancechecker.models;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import sdu.attendancechecker.dto.Attandance;
import sdu.attendancechecker.dto.Group;
import sdu.attendancechecker.dto.Subject;
import sdu.attendancechecker.dto.Student;
import sdu.attendancechecker.dto.Teacher;
import sdu.attendancechecker.services.DbService;

public class SubjectModel {

	/**
	 * 
	 * @param s
	 * @return
	 */

	public static boolean addSubject(Subject s) {
		/*
		 * Connection conn = DbService.getConnection(); if (conn == null) return
		 * false; String sql = "INSERT INTO subject(name, teacher_id, " +
		 * "classroom_id, week_day, time_start, time_end) " +
		 * "VALUES(?, ?, ?, ?, ?, ?);"; try { PreparedStatement pstmnt =
		 * conn.prepareStatement(sql); pstmnt.setString(1, s.getName());
		 * pstmnt.setInt(2, s.getTeacherId()); pstmnt.setInt(3,
		 * s.getClassroomId()); pstmnt.setInt(4, s.getWeekDay());
		 * pstmnt.setInt(5, s.getTimeStart()); pstmnt.setInt(6, s.getTimeEnd());
		 * pstmnt.executeUpdate(); return true; } catch (SQLException e) {
		 * e.printStackTrace(); return false; }
		 */
		return false;
	}

	public static ArrayList<Group> getGroupsByStudents(
			ArrayList<Integer> students) {
		try {
			Connection conn = DbService.getConnection();
			if (conn == null) {
				return null;
			}
			String sql = "SELECT * FROM student";
			ArrayList<Group> groups = new ArrayList<Group>();
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.execute();
			ResultSet result = pstmnt.getResultSet();
			while (result.next()) {
				int id = result.getInt("id");
				String name = result.getString("name");
				String surname = result.getString("surname");
				String group = result.getString("group");
				boolean flag = false;
				for (int i = 0; i < students.size(); i++) {
					if (students.get(i) == id)
						flag = true;
				}
				if (flag) {
					flag = false;
					for (int i = 0; i < groups.size(); i++) {
						if (groups.get(i).getName().equals(group)) {
							groups.get(i).getStudents()
									.add(new Student(id, name, surname));
							flag = true;
						}
					}
					if (!flag) {
						groups.add(new Group(group, new ArrayList<Student>()));
						groups.get(groups.size() - 1).getStudents()
								.add(new Student(id, name, surname));
					}
				}
			}
			return groups;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static ArrayList<Subject> getSubjects(int teacher) {
		try {
			Connection conn = DbService.getConnection();
			if (conn == null) {
				return null;
			}
			String sql = "SELECT subject FROM schedule WHERE teacher = ?";
			String sql2 = "SELECT * FROM subject WHERE id = ?";
			ArrayList<Subject> subjectsList = new ArrayList<Subject>();
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setInt(1, teacher);
			pstmnt.execute();
			ResultSet result = pstmnt.getResultSet();
			while (result.next()) {
				String subject = result.getString("subject");
				boolean isContains = false;
				for (int i = 0; i < subjectsList.size(); i++) {
					if (subjectsList.get(i).getId().equals(subject)) {
						isContains = true;
						break;
					}
				}
				if (!isContains) {
					PreparedStatement statement = conn.prepareStatement(sql2);
					statement.setString(1, subject);
					statement.execute();
					ResultSet result2 = statement.getResultSet();
					if (result2.next()) {
						subjectsList.add(new Subject(result2.getString("id"),
								result2.getString("name")));
					}
				}
			}
			return subjectsList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static ArrayList<Attandance> getAttendance(
			ArrayList<Student> students, String subject) {
		System.out.println(students.toString() + " model");
		try {
			Connection conn = DbService.getConnection();
			if (conn == null) {
				return null;
			}
			String sql = "SELECT * FROM `attendance` WHERE subject = ? ORDER BY date ASC";
			ArrayList<Attandance> attendance = new ArrayList<Attandance>();
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setString(1, subject);
			pstmnt.execute();
			ResultSet result = pstmnt.getResultSet();
			while (result.next()) {
				int student = result.getInt("student");
				boolean have = false;
				for (int i = 0; i < students.size(); i++) {
					if (students.get(i).getId() == student) {
						have = true;
						break;
					}
				}
				if (have) {
					boolean iscont = false;
					for (int i = 0; i < attendance.size(); i++) {
						if (attendance.get(i).getStudent() == student) {
							attendance.get(i).getDates()
									.add(result.getString("date"));
							attendance.get(i).getTimes()
									.add(result.getString("time"));
							attendance.get(i).getWeeks()
									.add(result.getInt("week"));
							attendance.get(i).getAtt()
									.add(result.getInt("att"));
							iscont = true;
							break;
						}
					}
					if (!iscont) {
						attendance.add(new Attandance(student,
								new ArrayList<String>(),
								new ArrayList<String>(),
								new ArrayList<Integer>(),
								new ArrayList<Integer>()));
						attendance.get(attendance.size() - 1).getDates()
								.add(result.getString("date"));
						attendance.get(attendance.size() - 1).getTimes()
								.add(result.getString("time"));
						attendance.get(attendance.size() - 1).getWeeks()
								.add(result.getInt("week"));
						attendance.get(attendance.size() - 1).getAtt()
								.add(result.getInt("att"));
					}

				}
			}
			for (int i = 0; i < students.size(); i++) {
				boolean iscon = false;
				for (int j = 0; j < attendance.size(); j++) {
					if (students.get(i).getId() == attendance.get(j)
							.getStudent()) {
						iscon = true;
						break;
					}
				}
				if (!iscon) {
					attendance
							.add(new Attandance(students.get(i).getId(),
									new ArrayList<String>(),
									new ArrayList<String>(),
									new ArrayList<Integer>(),
									new ArrayList<Integer>()));
				}
			}
			return attendance;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static ArrayList<Integer> getStudentsByTeacherAndClass(int teacher,
			String subject) {
		try {
			Connection conn = DbService.getConnection();
			if (conn == null) {
				return null;
			}
			String sql = "SELECT student FROM course_registration WHERE teacher = ? AND subject = ?";
			ArrayList<Integer> students = new ArrayList<Integer>();
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setInt(1, teacher);
			pstmnt.setString(2, subject);
			pstmnt.execute();
			ResultSet result = pstmnt.getResultSet();
			while (result.next()) {
				int student = result.getInt("student");
				students.add(student);
			}
			return students;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	/*
	 * 
	 * public static ArrayList<Schedule> getSubjectsByTime(String sortby, String
	 * fweek, String from, int i) { try { Connection conn =
	 * DbService.getConnection(); if (conn == null) { return null; } String sql
	 * = "SELECT * FROM schedule WHERE week = ? AND ? < start ORDER BY start";
	 * ArrayList<Schedule> res = new ArrayList<Schedule>(); PreparedStatement
	 * pstmnt = conn.prepareStatement(sql); pstmnt.setString(1, fweek);
	 * pstmnt.setString(2, from); pstmnt.execute(); ResultSet result =
	 * pstmnt.getResultSet(); while (result.next()) { Schedule s = new
	 * Schedule(); s.setId(result.getInt("id"));
	 * s.setTeacher(result.getInt("teacher")); res.add(s); } return res; } catch
	 * (SQLException e) { e.printStackTrace(); return null; } }
	 */
}
